import logging
import pymysql
from modules.storage.base.storage_base import StorageBase


class MysqlStore(StorageBase):
    def __init__(self, host, port,account, password, database):
        self.db = None
        self.cursor = None
        StorageBase.__init__(self, host=host, port=port, account=account, password=password, database=database)

    def build_connect(self) -> pymysql.cursors.Cursor:
        """
        建立数据库连接。

        Returns:
            pymysql.cursors.Cursor: 数据库游标对象，用于执行 SQL 语句。
        """
        try:
            self.db = pymysql.connect(host=self.host, user=self.account, password=self.password, port=self.port,
                                      database=self.database)
            self.cursor = self.db.cursor()

        except Exception as e:
            print(f"连接失败：{e}")
            self.db = None
            self.cursor = None
        else:
            print("连接成功")

        return self.cursor

    # 增
    # table 表
    # data 新增数据 键值对对象
    def add(self, table_collection_index, data, id=None):
        # data.keys()返回的是键的数组
        keys = ', '.join(data.keys())
        # 下面这段是构造多个%s最为占位符，有几个字段就构造几个
        values = ', '.join(['%s'] * len(data))
        # 要执行的sql语句
        sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table_collection_index, keys=keys, values=values)
        try:
            self.cursor.execute(sql, tuple(data.values()))
            self.db.commit()
            print(f"新增成功")
        except Exception as e:
            print("失败：{0}".format(e))
            self.db.rollback()
        finally:
            print(f"{sql}")

    # 没有新增，有就更新
    # table 表
    # data 新增数据 键值对对象
    def add_or_update(self, table_collection_index, data, condition=None, id=None):
        # data.keys()返回的是键的数组
        keys = ', '.join(data.keys())
        # 下面这段是构造多个%s最为占位符，有几个字段就构造几个
        values = ', '.join(['%s'] * len(data))
        # 要执行的sql语句
        # 如果主键已经存在，就执行更新操作
        sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE '.format(table=table_collection_index, keys=keys,
                                                                                              values=values)
        update = ', '.join(["{key} = %s".format(key=key) for key in data])
        sql += update

        try:
            self.cursor.execute(sql, tuple(data.values()) * 2)
            self.db.commit()
            print(f"新增成功")
        except Exception as e:
            print("失败：{0}".format(e))
            self.db.rollback()
        finally:
            print(f"{sql}")

    # 删
    # table 表名
    # condition 删除条件
    def delete(self, table_collection_index, condition=None, data=None, id=None):
        sql = 'DELETE FROM  {table} WHERE {condition}'.format(table=table, condition=condition)
        try:
            self.cursor.execute(sql)
            self.db.commit()
            print(f"删除成功")
        except Exception as e:
            print("失败：{0}".format(e))
            self.db.rollback()
        finally:
            print(f"{sql}")

    # 改
    # table 表名
    # data 数据
    # condition 条件
    def update(self, table_collection_index, data=None, condition=None, id=None, update_condition=None):
        # data.keys()返回的是键的数组
        keys = ', '.join(data.keys())
        # 下面这段是构造多个%s最为占位符，有几个字段就构造几个
        values = ', '.join(['%s'] * len(data))
        # 要跟新的数据
        update = ','.join(["{key} = %s".format(key=key) for key in data])
        # 要执行的sql语句
        sql = 'UPDATE {table} SET {update} WHERE {condition}'.format(table=table_collection_index, update=update, condition=condition)

        try:
            self.cursor.execute(sql, tuple(data.values()))
            self.db.commit()
            print(f"更新成功")
        except Exception as e:
            print("失败：{0}".format(e))
            self.db.rollback()
        finally:
            print(f"{sql}")

    # 查
    def search(self, table_collection_index, condition=None):
        # 要执行的sql语句
        sql = 'SELECT * FROM {table} WHERE {condition}'.format(table=table_collection_index, condition=condition)

        try:
            self.cursor.execute(sql)
            # fetchall获取结果的所有数据
            results = self.cursor.fetchall()
            # fetchall得到的是二重元祖，其中每个元素都是一条记录
            # 要注意的是fetch的内部实现中有一个偏移指针，用来指向查询结果，偏移指针最开始指向第一条数据，取了一次数据后，指针偏移到下一条数据，
            # fetchone被调用后，结果的偏移指针会指向下一条数据，fetchall方法返回的是从偏移指针指向的数据一直到结束的所有数据，所有fetchall获取的数据会少一条

            for row in results:
                print(row)

        except Exception as e:
            print("失败：{0}".format(e))


if __name__ == "__main__":
    # 建立连接
    mysql_connect = MysqlStore()
    mysql_connect.build_connect()
    data = {
        "name": "Bob",
        "age": 22
    }
    table = "students"
    mysql_connect.search(table, 'age>=20')
